﻿Public Class ChuyenBay
    Inherits DataProvider

    Public Function lay_dataset_chuyen_bay() As DataSet

        Dim sqlstr As String = "SELECT cb.id, sb2.ten as  `Sân bay đi`, sb.ten as sanbayden, cb.ngaybay FROM (ChuyenBay as cb INNER JOIN SanBay as sb ON sb.ID = cb.SanBayDen) inner join sanbay as sb2 on cb.sanbaydi = sb2.id"
        Return GetDT(sqlstr)

    End Function
    Public Function lay_dataset_chuyen_bay(ByVal machuyenbay As String, ByVal sanbaydi As String, ByVal sanbayden As String, ByVal ngaybay As String) As DataSet

        Dim sqlstr As String = "SELECT cb.id as `Mã chuyến bay`, sb2.ten as `Sân bay đi`, sb.ten as `Sân bay đến`, cb.ngaybay as `Ngày bay` FROM (ChuyenBay as cb INNER JOIN SanBay as sb ON sb.ID = cb.SanBayDen) inner join sanbay as sb2 on cb.sanbaydi = sb2.id"

        Dim where As String = ""
        Dim sql_sanbaydi As String = ""
        Dim sql_sanbayden As String = ""
        Dim sql_ngaybay As String = ""

        If Not String.IsNullOrEmpty(machuyenbay) Then
            If String.IsNullOrEmpty(where) Then
                where = " where cb.id = " + machuyenbay
            Else
                where = where + " and cb.id = " + machuyenbay
            End If
        End If

        If Not String.IsNullOrEmpty(sanbaydi) Then
            If String.IsNullOrEmpty(where) Then
                where = " where cb.sanbaydi = " + sanbaydi
            Else
                where = where + " and cb.sanbaydi = " + sanbaydi
            End If
        End If

        If Not String.IsNullOrEmpty(sanbayden) Then
            If String.IsNullOrEmpty(where) Then
                where = " where cb.sanbayden = " + sanbayden
            Else
                where = where + " and cb.sanbayden = " + sanbayden
            End If
        End If

        If Not String.IsNullOrEmpty(ngaybay) Then
            If String.IsNullOrEmpty(where) Then
                where = " where cb.ngaybay = #" + ngaybay + "#"
            Else
                where = where + " and cb.ngaybay = #" + ngaybay + "#"
            End If
        End If

        sqlstr = sqlstr + where
        Return GetDT(sqlstr)

    End Function


    Public Function Insert(ByVal dto_chuyenbay As DTO.ChuyenBay) As Integer
        Dim sqlstr As String = "INSERT INTO ChuyenBay(SanBayDi,SanBayDen,NgayBay,ThoiGianBay) VALUES('" + dto_chuyenbay.sanbaydi + "', '" + dto_chuyenbay.sanbayden + "', #" + dto_chuyenbay.ngaybay + "#, " + dto_chuyenbay.thoigianbay + ")"
        If IsNothing(Me.SaveData(sqlstr)) Then
            Return Nothing
        End If
        Dim id As Integer = Integer.Parse(GetValue("SELECT max(id) from chuyenbay"))
        Return id
    End Function

End Class
